package shg.book.database
{
	import com.shuhg.bean.BookBean;
	import com.shuhg.bean.BookParameter;
	import com.shuhg.book.tool.LeftBook;
	import com.shuhg.book.util.BitmapByteUtil;
	
	import flash.data.SQLConnection;
	import flash.data.SQLResult;
	import flash.data.SQLStatement;
	import flash.display.Bitmap;
	import flash.display.BitmapData;
	import flash.errors.SQLError;
	import flash.events.Event;
	import flash.events.SQLErrorEvent;
	import flash.events.SQLEvent;
	import flash.filesystem.File;
	import flash.filesystem.FileMode;
	import flash.filesystem.FileStream;
	import flash.geom.Matrix;
	import flash.net.dns.AAAARecord;
	import flash.utils.ByteArray;
	import flash.utils.Timer;
	
	import mx.collections.ArrayCollection;
	import mx.controls.Image;
	import mx.graphics.ImageSnapshot;
	import mx.graphics.codec.JPEGEncoder;
	import mx.utils.UIDUtil;
	
	import shg.book.fitment.FitmentSHG;
	import shg.book.loadImage.LoadImage;
	import shg.book.size.BookData;
	import shg.book.socket.SocketDate;
	import shg.book.util.ImageLoader;
	import shg.book.view.ReadTextBook;
	
	/**
	 * 当阅读器启动的时候该类将会被调用！如果当前登录这个用户是第一次在本机启动的话就会创建
	 * 一个以当前登录的用户名创建一个数据库，如果该用户以前登录过的话就需要查询当前登录这个用
	 * 户的以前留在本地上的数据，把他搜索显示在桌面上。同时也会到服务器撒谎能够获取数据信息
	 * shuhg这个数据库里面的所有数据都是共享的
	 * */
	public class ConnectionDatabase
	{
		//异步打开文件
		static private var conn:SQLConnection;
		
		private var loaderFile:ImageLoader;
		private var bookArray:ArrayCollection;
		private var fitement:FitmentSHG;
		private var readTextBook:ReadTextBook;
		//基本路径
		private var basePath:String="assets\\database\\";
		//更新桌面视图需要
		public var loadImage:LoadImage;
		//封面实体映射信息
		public var bookBean:BookBean;
		//但前页
		public var currentPage:Number=0;
		
		public function ConnectionDatabase()
		{
		}
		public function loaderSwf(event:Event):void{
			try{
				var imageWidth:uint = event.currentTarget.width;
				var imageHeigh:uint = event.currentTarget.height;
				//创建一个具有指定高度和宽度的BitmapData对象75/100
				var srcBmp:BitmapData = new BitmapData(BookData._WIDTH,BookData._HEIGTH);
				//或者指定路径的图片
				var m:Matrix=new Matrix();
				m.scale(BookData._WIDTH/imageWidth,BookData._HEIGTH/imageHeigh);
				srcBmp.draw(Image(event.currentTarget),m);
				var jpg:JPEGEncoder = new JPEGEncoder(100);
				var bytes:ByteArray = jpg.encode(srcBmp);
				//插入之前先把原来的数据删除掉
				delect(BookData.bookID);
				//插入数据
				//insert(0,0,BookData.bookID,BookData.filePath,bytes,"shg");
			}catch(error:Error){
				trace("加载文件到数据库失败");
			}
		}
		/**
		 * 连接数据库
		 * */
		public function initApp(flexBook:shuhg=null,userName:String=null):void{
			try{
				conn = new SQLConnection(); 
				conn.addEventListener(SQLEvent.OPEN, openSuccess); 
				conn.addEventListener(SQLErrorEvent.ERROR, openFailure); 
				var dbFile:File =File.applicationDirectory.resolvePath(basePath+SocketDate.userName+".db");// File.applicationDirectory.resolvePath("Users//SHUHG.DB"); 
				conn.open(dbFile);
				trace(dbFile.nativePath);
			}catch(error:Error){
				trace("初始化数据库文件失败");
			}
		}
		/**
		 * 如果连接上数据最后就
		 * */
		private function openSuccess(event:SQLEvent):void{
			try{
				var createStmt:SQLStatement = new SQLStatement(); 
				createStmt.sqlConnection = SQLConnection(event.currentTarget);
				//这是一个刚刚的的数据库表，里面的数据哪个用户都可以访问
				var sql:String =  
					"CREATE TABLE IF NOT EXISTS huahuang (" +  
					"    BOOKID TEXT, " +  			//书籍的ID
					"    VERSION INTEGER, " +  			//书籍的收费类型
					"    BOOKNAME TEXT," +  		//书籍名称
					"    AUTHORNAME TEXT," +  		//书籍名称 
					"    PAGECOUNT NUMBER," +  		//书籍名称
					"    BOOKINDEX INTEGER," +  			//书籍所在位置
					"	 INDEXPAGE INTEGER," +	//书籍翻到哪一页
					"    FILEPATH TEXT," +  		//书籍所在路径
					"    OPENTIME NUMBER," +  		//书籍所在路径
					"    IMAGE  BLOB" +  			//书籍的封面路径
					")"; 
				createStmt.text = sql; 
				createStmt.addEventListener(SQLEvent.RESULT, createResult); 
				createStmt.addEventListener(SQLErrorEvent.ERROR, createError); 
				createStmt.execute(); 
				//从数据库里面查询数据信息
				select();
			}catch(error:SQLError){
				trace("连接数据库出现异常"+error);
			}
		}
		/**
		 * 插入数据
		 * */
		private function insert():void{
			try{
				var insertStmt:SQLStatement = new SQLStatement(); 
				insertStmt.sqlConnection = conn; 
				var sql:String =  
					"INSERT INTO huahuang (BOOKID,VERSION,BOOKNAME,AUTHORNAME,PAGECOUNT,BOOKINDEX,INDEXPAGE,FILEPATH,OPENTIME,IMAGE ) " +  
					"VALUES (:BOOKID,:VERSION,:BOOKNAME,:AUTHORNAME,:PAGECOUNT, :BOOKINDEX,:INDEXPAGE,:FILEPATH,:OPENTIME, :IMAGE)"; 
				insertStmt.parameters[":BOOKID"]=bookBean.bookId;
				insertStmt.parameters[":VERSION"]=bookBean.version;
				insertStmt.parameters[":BOOKNAME"]=bookBean.bookName;
				insertStmt.parameters[":AUTHORNAME"]=bookBean.authorName;
				insertStmt.parameters[":PAGECOUNT"]=bookBean.pageCount;
				insertStmt.parameters[":BOOKINDEX"]=bookBean.bookIndex;
				insertStmt.parameters[":INDEXPAGE"]=bookBean.indexPage;
				insertStmt.parameters[":FILEPATH"]=bookBean.filePath;
				insertStmt.parameters[":OPENTIME"]=getTime();
				insertStmt.parameters[":IMAGE"]=bookBean.coverByte;
				insertStmt.text = sql; 
				// register listeners for the result and failure (status) events 
				insertStmt.addEventListener(SQLEvent.RESULT, insertResult); 
				insertStmt.addEventListener(SQLErrorEvent.ERROR, insertError); 
				// execute the statement 
				insertStmt.execute(); 
			}catch(error:Error){
				trace("插入数据出现了异常"+error);
			}
			
		}
		
		/**
		 * 查询数据库表里面的数据
		 * */
		private function select():void{
			try{
				var selectStmt:SQLStatement = new SQLStatement(); 
				selectStmt.sqlConnection = conn; 
				var sql:String = "SELECT BOOKID,VERSION,BOOKNAME,AUTHORNAME,PAGECOUNT,BOOKINDEX,INDEXPAGE,FILEPATH,OPENTIME,IMAGE FROM huahuang ORDER BY OPENTIME DESC"; 
				selectStmt.text = sql; 
				selectStmt.addEventListener(SQLEvent.RESULT, resultHandler); 
				selectStmt.addEventListener(SQLErrorEvent.ERROR, errorHandler); 
				selectStmt.execute(); 
			}catch(error:Error){
				trace("查询数据库表里面的数据出现了异常");
			}
		}
		
		/**
		 * 上面查询的返回结果
		 * */
		private function resultHandler(event:SQLEvent):void{ 
			try{
				var result:SQLResult = event.currentTarget.getResult(); 
				if(result.data!=null){
					var numResults:Number = result.data.length; 
					//var array:Array=new Array();
					var loadImage:LoadImage=new LoadImage();
					for (var i:int = 0; i < numResults; i++) 
					{
						var row:Object = result.data[i]; 
						var bookBean:BookBean=getBookBean(row);
						//更新视图(如果文件不存在了就没必要现在在桌面了，并要删除数据库里面对应的数据)
						if(new File(bookBean.filePath).exists){
							loadImage.flexBook = shuhg.flexBook;
							loadImage.openImage(bookBean);
						}else{
							delect(bookBean.bookId);
						}
					} 
					//查询前面4本
					selectPre();
				}else{
					trace("没有可查询的数据");
				}
			}catch(error:Error){
				trace("上面查询的返回结果出现了异常");
			}
		} 
		/**
		 * 查询前面4本书籍
		 * */
		public function selectPre():void{
			try{
				var selectStmt:SQLStatement = new SQLStatement(); 
				selectStmt.sqlConnection = conn; 
				var sql:String = "SELECT *  FROM huahuang ORDER BY OPENTIME DESC LIMIT 4"; 
				selectStmt.text = sql; 
				selectStmt.addEventListener(SQLEvent.RESULT, resultLeftHandler); 
				selectStmt.addEventListener(SQLErrorEvent.ERROR, errorHandler); 
				selectStmt.execute(); 
			}catch(error:Error){
				trace("查询数据库表里面的数据出现了异常");
			}
		}
		/**
		 * 返回前4本的查询结果
		 * */
		private function resultLeftHandler(event:SQLEvent):void{ 
			try{
				var result:SQLResult = event.currentTarget.getResult(); 
				if(result.data!=null){
					var numResults:Number = result.data.length; 
					//清空
					shuhg.flexBook.leftWinTool.leftBookShow.removeAllElements();
					for (var i:int = 0; i < numResults; i++) 
					{
						var row:Object = result.data[i]; 
						var bookBean:BookBean=getBookBean(row);
						//更新视图(如果文件不存在了就没必要现在在桌面了，并要删除数据库里面对应的数据)
						if(!(new File(bookBean.filePath).exists)){
							delect(bookBean.bookId);
						}
						//把前面书本书籍存储在左边
						history(bookBean);
					} 
				}else{
					trace("没有可查询的数据");
				}
			}catch(error:Error){
				trace("上面查询的返回结果出现了异常");
			}
		} 
		/**
		 * 将从数据库查询出来的数据转为BookBean
		 * */
		private function getBookBean(row:Object):BookBean{
			var bookBean:BookBean=new BookBean();
			bookBean.bookId = row.BOOKID;
			bookBean.version = row.VERSION;
			bookBean.bookName = row.BOOKNAME;
			bookBean.authorName = row.AUTHORNAME;
			bookBean.pageCount = row.PAGECOUNT;
			bookBean.bookIndex = row.BOOKINDEX;
			bookBean.indexPage = row.INDEXPAGE;
			bookBean.filePath = row.FILEPATH;
			bookBean.coverByte = row.IMAGE;
			//将字节转为位图
			bookBean.coverBitmap = BitmapByteUtil.ByteArrayToBitmap(bookBean.coverByte);
			return bookBean;
		}
		
		/**
		 * 把前面4本数据存储在前面左边
		 * */
		public function history(bookBean:BookBean):void{
			try{
				var leftBook:LeftBook = new LeftBook();
				leftBook.bookBean = bookBean;
				shuhg.flexBook.leftWinTool.leftBookShow.addElement(leftBook);
			}catch(error:Error){
				trace("抛出异常",error);
			}
		}
		/**
		 * 当用户点击硬盘里面的某本书籍的时候，首先
		 * 应该检查该数据是否存在
		 * */
		public function checkBook():void{
			try{
				this.readTextBook=readTextBook;
				var selectStmt:SQLStatement = new SQLStatement(); 
				selectStmt.sqlConnection = conn; 
				var sql:String = "SELECT * FROM huahuang WHERE BOOKID='"+bookBean.bookId+"'"; 
				selectStmt.text = sql; 
				selectStmt.addEventListener(SQLEvent.RESULT, resultBook); 
				selectStmt.addEventListener(SQLErrorEvent.ERROR, errorHandler); 
				selectStmt.execute();
			}catch(error:Error){
				trace("根据书籍的ID检查书籍出现异常");
			}
		}
		/**
		 * 返回书籍的查询结果
		 * */
		private function resultBook(event:SQLEvent):void{
			try{
				var result:SQLResult = event.currentTarget.getResult(); 
				if(result.data==null){
					//如果这本书籍在数据库里面不存在就里面把视图更新到桌面
					loadImage.flexBook=shuhg.flexBook;
					bookBean.bookIndex = loadImage.openImage(bookBean);
					//同时将这本书籍保存进入数据库里面
					insert();
//					var numResults:Number = result.data.length; 
//					var array:Array=new Array();
//					for (var i:int = 0; i < numResults; i++) 
//					{ 
//						var row:Object = result.data[i]; 
//						trace(" ID ",row.ID);
//						trace(" BOOKNAME ",row.BOOKNAME);
//						trace(" BOOKINDEX ",row.BOOKINDEX);
//						trace(" INDEXPAGE ",row.INDEXPAGE);
//						trace(" FILEPATH",row.FILEPATH);
//					} 
				}else{
					trace(" 该数据已经存在数据库里面了");
				}
			}catch(error:SQLError){
				trace("返回书籍的查询结果出现"+error.toString());
			}
		}
		/**
		 * 根据书籍的ID号查询index
		 * */
		public function selectForID(id:String):Number{
			try{
				var selectStmt:SQLStatement = new SQLStatement(); 
				selectStmt.sqlConnection = conn; 
				var sql:String = "SELECT INDEXPAGE FROM huahuang WHERE BOOKID='"+id+"'"; 
				selectStmt.text = sql; 
				selectStmt.addEventListener(SQLEvent.RESULT, resultIndex); 
				selectStmt.addEventListener(SQLErrorEvent.ERROR, errorHandler); 
				selectStmt.execute();
			}catch(error:Error){
				trace("根据书籍的ID号查询index异常");
			}
			return currentPage;
		}
		/**
		 * 返回数据结果
		 * */
		private function resultIndex(event:SQLEvent):void{
			try{
				var result:SQLResult = event.currentTarget.getResult(); 
				if(result.data!=null){
					var numResults:Number = result.data.length; 
					var array:Array=new Array();
					for (var i:int = 0; i < numResults; i++) 
					{ 
						var row:Object = result.data[i]; 
						currentPage = row.INDEXPAGE;
					} 
				}else{
					trace("没有可查询的数据");
				}
			}catch(error:Error){
				trace("返回数据结果异常");
			}
			
		}
		/**
		 * 更新数据库表里面的数据
		 **/
		public function update(ID:String,bookIndex:Number):void{
			try{
				var updateStmt:SQLStatement = new SQLStatement(); 
				updateStmt.sqlConnection = conn; 
				var sql:String = "UPDATE huahuang SET BOOKINDEX="+bookIndex+",OPENTIME="+getTime()+" where BOOKID='"+ID+"'";
				updateStmt.text = sql; 
				// register listeners for the result and failure (status) events  75D6869B-9654-0C9E-CA13-9D6385B2FF3F
				updateStmt.addEventListener(SQLEvent.RESULT, updateStmtHandler); 
				updateStmt.addEventListener(SQLErrorEvent.ERROR, updateStmtError); 
				// execute the statement 
				updateStmt.execute(); 
				
				trace("更新书籍当前页成功");
			}catch(error:Error){
				trace("更新数据库表里面的数据异常");
			}
			
		} 
		/**
		 * 更新数据库表里面的数据，把当前这本书籍翻到那一页
		 * 保存进入数据库表
		 * */
		public function updatePageIndex(ID:String,pageIndex:int):void{
			try{
				var updateStmt:SQLStatement = new SQLStatement(); 
				updateStmt.sqlConnection = conn; 
				var sql:String = "UPDATE huahuang SET INDEXPAGE="+pageIndex+",OPENTIME="+getTime()+" where BOOKID='"+ID+"'";
				updateStmt.text = sql; 
				// register listeners for the result and failure (status) events 
				updateStmt.addEventListener(SQLEvent.RESULT, updateStmtHandler); 
				updateStmt.addEventListener(SQLErrorEvent.ERROR, updateStmtError); 
				// execute the statement 
				updateStmt.execute(); 
				
				selectPre();
			}catch(error:Error){
				trace("更新数据库表里面的数据，把当前这本书籍翻到那一页异常");
			}
			
			
		}
		/**
		 * 删除数据库表里面的数据
		 * */
		private function delect(bookID:String):void{
			try{
				var detectStmt:SQLStatement = new SQLStatement(); 
				detectStmt.sqlConnection = conn; 
				var sql:String = "DELETE FROM huahuang WHERE BOOKID='"+bookID+"'"; 
				detectStmt.text = sql; 
				// register listeners for the result and failure (status) events 
				detectStmt.addEventListener(SQLEvent.RESULT, detectHandler); 
				detectStmt.addEventListener(SQLErrorEvent.ERROR, detectError); 
				// execute the statement 
				detectStmt.execute(); 
			}catch(error:Error){
				trace("删除数据库表里面的数据异常");
			}
			
		}
		/**
		 * 获取当前时间
		 * */
		private function getTime():Number{
			var date:Date = new Date();
			return date.time;
		}
		private function openFailure(event:SQLErrorEvent):void{
			trace("操作数据文件产生异常！"+event.toString());
		}
		
		private function createResult(event:SQLEvent):void 
		{ 
			trace("Table created"); 
		} 
		
		private function createError(event:SQLErrorEvent):void 
		{ 
			trace("Error message:", event.error.message); 
			trace("Details:", event.error.details); 
		} 
		
		private function detectHandler(event:SQLEvent):void{
			trace("删除数据成功！");
		}
		private function detectError(event:SQLErrorEvent):void{
			trace(" 删除数据产生异常!");
		}
		private function insertResult(event:SQLEvent):void 
		{ 
			trace("INSERT statement succeeded"); 
		} 
		
		private function insertError(event:SQLErrorEvent):void 
		{ 
			trace("Error message:", event.error.message); 
			trace("Details:", event.error.details); 
		} 
		private function errorHandler(event:SQLErrorEvent):void{ 
			trace("搜索数据产生异常！");
		}
		private function updateStmtHandler(event:SQLEvent):void{
			trace("更新数据成功!!");
		}
		private function updateStmtError(event:SQLErrorEvent):void{
			trace("更新数据失败");
		}
	}
}